# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html
import json
import pymysql
from scrapy.utils.project import get_project_settings

class DoubanPipeline(object):
    def __init__(self):
        # 文件的打开写到这里，仅会执行一次
        self.fp = open('douban_general_spider.txt', 'w', encoding='utf-8')
        
    def open_spider(self, spider):
        pass

    # 在这里处理每一个item
    def process_item(self, item, spider):
        obj = dict(item)
        item['spider_name'] = spider.name
        string = json.dumps(obj, ensure_ascii=False)
        self.fp.write(string + '\n')
        return item

    # 重写这个方法，在关闭spider的时候将文件资源关闭
    def close_spider(self, spider):
        self.fp.close()

# 存入到mysql中
class MysqlPipeline(object):
    """docstring for MysqlPipeline"""
    def __init__(self):
        settings = get_project_settings()
        self.host = settings['DB_HOST']
        self.port = settings['DB_PORT']
        self.user = settings['DB_USER']
        self.pwd = settings['DB_PWD']
        self.name = settings['DB_NAME']
        self.charset = settings['DB_CHARSET']
        # 链接数据库
        self.connect()

    def connect(self):
        self.conn = pymysql.connect(host=self.host,
                             port=self.port,
                             user=self.user,
                             password=self.pwd,
                             db=self.name,
                             charset=self.charset)
        self.cursor = self.conn.cursor()

    def close_spider(self, spider):
        self.conn.close()
        self.cursor.close()

    def process_item(self, item, spider):
        # 查重操作
        ckeck_sql = '''SELECT * FROM A_Douban_Zufang WHERE href = "{}" '''.format(item['href'])
        # ckeck_sql = '''SELECT * FROM A_Douban_Zufang LIMIT 1 '''
        self.cursor.execute(ckeck_sql)
        res = self.cursor.fetchone()
        # print('kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk标记')
        # print(res)
        if not res:
            sql = 'insert into A_Douban_Zufang(title, href, is_delete) values("%s", "%s", 0)' % (item['title'], item['href'])
            # 执行sql语句
            self.cursor.execute(sql)
            # 提交之后才会生效
            self.conn.commit()
            print('{}数据插入成功...'.format(item['href']))
        return item